﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.Configuration;
using System.ComponentModel.DataAnnotations;
using trangbaocao.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;

namespace trangbaocao.Models
{
    public class Manage_DslamModels
    {
        public Manage_DslamModels()
        { 
           
        }
        public List<Dslam> getAllDslam()
        {
            List<Dslam> dslam_ = new List<Dslam>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT a.ID, a.NAME, a.MAIN_SORT_DEVICE_ID,a.SORT_DEVICE_ID, b.NAME, a.MANUFACTORYS_ID, a.MO_TA_THIET_BI  FROM DEVICE a, SORT_DEVICE b  WHERE a.SORT_DEVICE_ID = b.ID and a.MAIN_SORT_DEVICE_ID=3 ORDER BY a.ID");
            while (odr.Read())
            {
                dslam_.Add(new Dslam { 
                device_id = Convert.ToInt32(odr[0].ToString()),
                name = odr[1].ToString(),
                main_sort_device_id = Convert.ToInt32(odr[2].ToString()),
                sort_device_id=Convert.ToInt32(odr[3].ToString()),
                name_sort_device = odr[4].ToString(),
                manufactorys_id=Convert.ToInt32(odr[5].ToString()),
                mo_ta_thiet_bi= odr[6].ToString()
                });
            }
            return dslam_;
        }
        public Dslam getDslamById(int id)
        {
            Dslam dslam_ = new Dslam();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT a.ID, a.NAME, a.MAIN_SORT_DEVICE_ID,a.SORT_DEVICE_ID, b.NAME, a.MANUFACTORYS_ID, a.MO_TA_THIET_BI  FROM DEVICE a, SORT_DEVICE b  WHERE a.SORT_DEVICE_ID = b.ID and a.ID="+id+" ORDER BY a.ID");
            while (odr.Read())
            {
                
                    dslam_.device_id = Convert.ToInt32(odr[0].ToString());
                    dslam_.name = odr[1].ToString();
                    dslam_.main_sort_device_id = Convert.ToInt32(odr[2].ToString());
                    dslam_.sort_device_id = Convert.ToInt32(odr[3].ToString());
                    dslam_.name_sort_device = odr[4].ToString();
                    dslam_.manufactorys_id = Convert.ToInt32(odr[5].ToString());
                    dslam_.mo_ta_thiet_bi = odr[6].ToString();
            }
            return dslam_;
        }
        public List<Paramenter_Base_Dslam> getParamentBase(int device_id)
        {
            List<Paramenter_Base_Dslam> listParamenter = new List<Paramenter_Base_Dslam>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT ID, DEVICE_ID, MAIN_SORT_DEVICE_ID, SORT_DEVICE_ID, MANUFACTORYS_ID, NAME_PARAMETER, UNIT, VALUE_MAX, NOTES, decode(KIEU_DU_LIEU,0,'Ký tự','Số') FROM PARAMETERRS_BASE WHERE DEVICE_ID =" + device_id);
            while (odr.Read())
            {
                listParamenter.Add(new Paramenter_Base_Dslam
                {
                    paramenter_base_id = Convert.ToInt32(odr[0].ToString()),
                    device_id= Convert.ToInt32(odr[1].ToString()),
                    main_sort_device_id = Convert.ToInt32(odr[2].ToString()),
                    sort_device_id = Convert.ToInt32(odr[3].ToString()),
                    manufactorys_id = Convert.ToInt32(odr[4].ToString()),
                    name_paramenter =odr[5].ToString(),
                    unit = odr[6].ToString(),
                    value_max = Convert.ToInt32(odr[7].ToString()),
                    notes= odr[8].ToString(),
                    datatype=odr[9].ToString()
                });
            }
            return listParamenter;
        }
        public int checkExist(string paramenter_base_name, int device_id)
        {
            string sql = "SELECT * FROM PARAMETERRS_BASE WHERE NAME_PARAMETER='" + paramenter_base_name + "' and device_id = " + device_id;
            return General.Lib_CheckExist(sql);
        }
        public int checkPraUsingExist(int paramenter_base_id, int device_stations_id)
        {
            string sql = "SELECT * FROM PARAMETER_USING WHERE device_stations_id=" + device_stations_id + " and parameter_base_id = " + paramenter_base_id;
            return General.Lib_CheckExist(sql);
        }
        public void AddParameterBaseDslam(Paramenter_Base_Dslam pra)
        {
            string sql = "INSERT INTO PARAMETERRS_BASE";
            sql += " select nvl((select max(id)+1 from PARAMETERRS_BASE),1),";
            sql += pra.device_id + ", " + pra.main_sort_device_id + ", " + pra.sort_device_id + ", ";
            sql += pra.manufactorys_id + ", '" + pra.name_paramenter + "','" + pra.unit + "'," + pra.value_max + ",'" + pra.notes + "', " + pra.pra_meba_sort_dv_id +", to_number('"+ pra.datatype + "') from dual";
            General.Lib_ExecuteNonQuery(sql);
        }
        public void UpdateParameterBaseDslam(Paramenter_Base_Dslam pra)
        {
            string sql = "UPDATE PARAMETERRS_BASE SET ";
            sql += "DEVICE_ID = " + pra.device_id;
            sql += ", MAIN_SORT_DEVICE_ID = " + pra.main_sort_device_id;
            sql += ", SORT_DEVICE_ID = " + pra.sort_device_id;
            sql += ", MANUFACTORYS_ID = "+ pra.manufactorys_id;
            sql += ", NAME_PARAMETER ='" + pra.name_paramenter + "'";
            sql += ", UNIT = '"+ pra.unit + "'";
            sql += ", VALUE_MAX = " + pra.value_max;
            sql += ", NOTES = '" + pra.notes + "'";
            sql += ", KIEU_DU_LIEU= to_number('" + pra.datatype + "')";
            sql += " WHERE ID=" + pra.paramenter_base_id;
            General.Lib_ExecuteNonQuery(sql);
        }
        public void DeleteParameterBaseDslam(int pra_id)
        {
            string sql = "DELETE PARAMETERRS_BASE WHERE ID = " + pra_id;
            General.Lib_ExecuteNonQuery(sql);
        }
        public List<Paramenter_Base_Dslam> getByParamenter_Base_device_id(int device_id)
        {
            List<Paramenter_Base_Dslam> par_base = new List<Paramenter_Base_Dslam>();
            string sql = "SELECT ID, DEVICE_ID, MAIN_SORT_DEVICE_ID, SORT_DEVICE_ID, MANUFACTORYS_ID, NAME_PARAMETER, UNIT, VALUE_MAX, NOTES FROM PARAMETERRS_BASE WHERE DEVICE_ID=" + device_id;
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                par_base.Add(new Paramenter_Base_Dslam
                {
                    paramenter_base_id = Convert.ToInt32(odr[0].ToString()),
                    device_id = Convert.ToInt32(odr[1].ToString()),
                    main_sort_device_id = Convert.ToInt32(odr[2].ToString()),
                    sort_device_id = Convert.ToInt32(odr[3].ToString()),
                    manufactorys_id = Convert.ToInt32(odr[4].ToString()),
                    name_paramenter = odr[5].ToString(),
                    unit = odr[6].ToString(),
                    value_max = Convert.ToInt32(odr[7].ToString()),
                    notes = odr[8].ToString()
                });
            }
            return par_base;
        }
        public List<Paramenter_Base_Dslam> getList_ByParamenter_Base_id(int id)
        {
            List<Paramenter_Base_Dslam> par_base = new List<Paramenter_Base_Dslam>();
            string sql = "SELECT ID, DEVICE_ID, MAIN_SORT_DEVICE_ID, SORT_DEVICE_ID, MANUFACTORYS_ID, NAME_PARAMETER, UNIT, VALUE_MAX, NOTES FROM PARAMETERRS_BASE WHERE ID=" + id;
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                par_base.Add(new Paramenter_Base_Dslam
                {
                    paramenter_base_id = Convert.ToInt32(odr[0].ToString()),
                    device_id = Convert.ToInt32(odr[1].ToString()),
                    main_sort_device_id = Convert.ToInt32(odr[2].ToString()),
                    sort_device_id = Convert.ToInt32(odr[3].ToString()),
                    manufactorys_id = Convert.ToInt32(odr[4].ToString()),
                    name_paramenter = odr[5].ToString(),
                    unit = odr[6].ToString(),
                    value_max = Convert.ToInt32(odr[7].ToString()),
                    notes = odr[8].ToString()
                });
            }
            return par_base;
        }
        public Paramenter_Base_Dslam getByParamenter_Base_id(int para_id)
        {
            Paramenter_Base_Dslam par_base = new Paramenter_Base_Dslam();
            string sql = "SELECT ID, DEVICE_ID, MAIN_SORT_DEVICE_ID, SORT_DEVICE_ID, MANUFACTORYS_ID, NAME_PARAMETER, UNIT, VALUE_MAX, NOTES, KIEU_DU_LIEU FROM PARAMETERRS_BASE WHERE ID=" + para_id;
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                par_base.paramenter_base_id = Convert.ToInt32(odr[0].ToString());
                par_base.device_id = Convert.ToInt32(odr[1].ToString());
                par_base.main_sort_device_id = Convert.ToInt32(odr[2].ToString());
                par_base.sort_device_id = Convert.ToInt32(odr[3].ToString());
                par_base.manufactorys_id = Convert.ToInt32(odr[4].ToString());
                par_base.name_paramenter = odr[5].ToString();
                par_base.unit = odr[6].ToString();
                par_base.value_max = Convert.ToInt32(odr[7].ToString());
                par_base.notes = odr[8].ToString();
                par_base.datatype = odr[9].ToString();
            }
            return par_base;
        }
        public string getNameDeviceStation(int device_station_id)
        {
            string sql = "select t.name from DEVICE_STATIONS t where t.id=" + device_station_id;
            string name = "";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                name = odr[0].ToString();
            }
            return name;
        }
        public List<Paramenter_Using_Dslam> getParamenterUsingDslam(int id_device_stations)
        {
            List<Paramenter_Using_Dslam> prus = new List<Paramenter_Using_Dslam>();
            string sql = "select a.ID, a.device_id, a.main_sort_device_id, a.sort_device_id, a.manufactorys_id, a.device_stations_id, a.parameter_base_id, decode(b.kieu_du_lieu,0,'Ký tự','Số'), a.value_using, a.date_setup, a.date_sys, b.name_parameter, a.note, b.unit ";
                   sql += "from PARAMETER_USING a, PARAMETERRS_BASE b where a.parameter_base_id = b.id and a.device_stations_id = "+id_device_stations+" order by a.id";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                prus.Add ( new Paramenter_Using_Dslam {
                    paramenter_using_id = Convert.ToInt32(odr[0].ToString()),
                    device_id = Convert.ToInt32(odr[1].ToString()),
                    main_sort_device_id = Convert.ToInt32(odr[2].ToString()),
                    sort_device_id = Convert.ToInt32(odr[3].ToString()),
                    manufactorys_id = Convert.ToInt32(odr[4].ToString()),
                    device_stations_id = Convert.ToInt32(odr[5].ToString()),
                    paramenter_base_id = Convert.ToInt32(odr[6].ToString()),
                    datatype = odr[7].ToString(),
                    value_using = odr[8].ToString(),
                    date_setup = odr.IsDBNull(9) ? null : odr.GetDateTime(9).ToString("dd/MM/yyyy"),
                    date_sys = odr.IsDBNull(10) ? null : odr.GetDateTime(10).ToString("dd/MM/yyyy"),
                    name_parament= odr[11].ToString(),
                    notes = odr[12].ToString(),
                    unit = odr[13].ToString()
                    });
            }
            return prus;
        }
        public List<Device_Stations_Parament_Using> dslam_stations_pra_us()
        {
            List<Device_Stations_Parament_Using> dv_st_pr_us = new List<Device_Stations_Parament_Using>();
            string sql = "";
            int quyen_id = Convert.ToInt16(HttpContext.Current.Session["quyen_id"]);
            int trungtam_id = Convert.ToInt16(HttpContext.Current.Session["trungtam_id"]);
            if (quyen_id > 2)
            {
                sql = "select a.id, a.name, b.name, c.name , to_char(a.ngay_lap_dat,'dd/mm/yyyy'), a.device_id from DEVICE_STATIONS a, SORT_DEVICE b, STATIONS c ";
                sql += "where a.sort_device_id = b.id and a.stations_id=c.id and a.main_sort_device_id = 3 and  a.main_stations_id = " + trungtam_id + " order by a.stations_id";
            }
            else
            {
                sql = "select a.id, a.name, b.name, c.name , to_char(a.ngay_lap_dat,'dd/mm/yyyy'), a.device_id from DEVICE_STATIONS a, SORT_DEVICE b, STATIONS c ";
                sql += "where a.sort_device_id = b.id and a.stations_id=c.id and a.main_sort_device_id = 3 order by a.stations_id";
            }

            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                dv_st_pr_us.Add(new Device_Stations_Parament_Using
                {
                    device_stations_id = Convert.ToInt32(odr[0].ToString()),
                    name_device_stations = odr[1].ToString(),
                    name_stations = odr[3].ToString(),
                    sort_device_name = odr[2].ToString(),
                    date_setup = odr[4].ToString(),
                    device_id = Convert.ToInt32(odr[5].ToString())
                });
            }
            return dv_st_pr_us;
        }
        public Device_Stations_Parament_Using dslam_stations_pra_us_id(int device_stations_id)
        {
            Device_Stations_Parament_Using dv_st_pr_us = new Device_Stations_Parament_Using();
            string sql = "select a.id, a.name, b.name, c.name , a.ngay_lap_dat, a.device_id from DEVICE_STATIONS a, SORT_DEVICE b, STATIONS c ";
            sql += "where a.sort_device_id = b.id and a.stations_id=c.id and a.id = " + device_stations_id + " order by a.stations_id";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                dv_st_pr_us.device_stations_id = Convert.ToInt32(odr[0].ToString());
                dv_st_pr_us.name_device_stations = odr[1].ToString();
                dv_st_pr_us.name_stations = odr[3].ToString();
                dv_st_pr_us.sort_device_name = odr[2].ToString();
                dv_st_pr_us.date_setup = odr[4].ToString();
                dv_st_pr_us.device_id = Convert.ToInt32(odr[5].ToString());
            }
            return dv_st_pr_us;
        }
        public List<Device_Stations_Parament_Using> dslam_stations_pra_us_where(string strwh)
        {
            List<Device_Stations_Parament_Using> dv_st_pr_us = new List<Device_Stations_Parament_Using>();
            string sql = "select a.id, a.name, b.name, c.name , to_char(a.ngay_lap_dat,'dd/mm/yyyy') from DEVICE_STATIONS a, SORT_DEVICE b, STATIONS c ";
            sql += "where a.sort_device_id = b.id and a.stations_id=c.id and a.main_sort_device_id = 3 " + strwh + " order by a.stations_id";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                dv_st_pr_us.Add(new Device_Stations_Parament_Using
                {
                    device_stations_id = Convert.ToInt32(odr[0].ToString()),
                    name_device_stations = odr[1].ToString(),
                    name_stations = odr[3].ToString(),
                    sort_device_name = odr[2].ToString(),
                    date_setup = odr[4].ToString()
                });
            }
            return dv_st_pr_us;
        }
        public string getNameDslam(int dslam_id)
        {
            string sql = "select t.name from DEVICE t where t.id=" + dslam_id;
            string name="";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                name = odr[0].ToString();
            }
            return name;

        }
       
        public Device_Stations getDeviceStationsById(int id)
        {
            string sql = "select t.id, t.device_id, t.sort_device_id, t.main_sort_device_id, t.manufactorys_id from DEVICE_STATIONS t where t.id=" + id;
            Device_Stations dvst = new Device_Stations();
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                dvst.device_station_id=  Convert.ToInt32(odr[0].ToString());
                dvst.device_id   = Convert.ToInt32(odr[1].ToString());
                dvst.main_sort_device_id = Convert.ToInt32(odr[3].ToString());
                dvst.sort_device_id = Convert.ToInt32(odr[2].ToString());
                dvst.manufactorys_id = Convert.ToInt32(odr[4].ToString());
            }
            return dvst;
        }
        public void AddParamenterUsingDslam(Paramenter_Using_Dslam parUs)
        {
            string sql = "INSERT INTO PARAMETER_USING";
            sql += " select nvl((select max(id)+1 from PARAMETER_USING),1),";
            sql += parUs.device_id + ", "+ parUs.main_sort_device_id + "," + parUs.sort_device_id + "," +parUs.manufactorys_id + ", "+ parUs.device_stations_id +", " + parUs.paramenter_base_id+", " + parUs.value_lapdat +", '" +parUs.value_using + "', to_date('" + parUs.date_setup + "','dd/mm/yyyy'), to_date('" + parUs.date_sys + "','dd/mm/yyyy'), '"+ parUs.notes + "' from dual";
            General.Lib_ExecuteNonQuery(sql);
        }
        public void DeleteParamenterUsingDslam(int id)
        {
            string sql = "DELETE PARAMETER_USING WHERE ID = " + id;
            General.Lib_ExecuteNonQuery(sql);
        }
        public Paramenter_Using_Dslam getParameterUsingById(int id)
        {
            string sql = "select t.id, t.device_id, t.main_sort_device_id, t.sort_device_id, t.manufactorys_id, t.device_stations_id, t.parameter_base_id, t.value_lapdat, t.value_using, t.date_setup, t.date_sys, t.note from PARAMETER_USING t WHERE t.id =" + id;
            Paramenter_Using_Dslam prud = new Paramenter_Using_Dslam();
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            { 
                prud.paramenter_using_id =  Convert.ToInt32(odr[0].ToString());
                prud.device_id = Convert.ToInt32(odr[1].ToString());
                prud.main_sort_device_id =Convert.ToInt32(odr[2].ToString());
                prud.sort_device_id = Convert.ToInt32(odr[3].ToString());
                prud.manufactorys_id = Convert.ToInt32(odr[4].ToString());
                prud.device_stations_id = Convert.ToInt32(odr[5].ToString());
                prud.paramenter_base_id = Convert.ToInt32(odr[6].ToString());
                prud.value_lapdat = Convert.ToInt32(odr[7].ToString());
                prud.value_using = odr[8].ToString();
                prud.date_setup =odr[9].ToString();
                prud.date_sys = odr[10].ToString();
                prud.notes = odr[11].ToString();
            }
            return prud;
        }
        public void Update_Parabase_Using_Dslam(Paramenter_Using_Dslam praUs)
        {
            string sql = "UPDATE PARAMETER_USING t SET ";
            sql += "t.device_id = " + praUs.device_id ;
            sql +=", t.main_sort_device_id=" + praUs.main_sort_device_id;
            sql +=", t.sort_device_id =" + praUs.sort_device_id;
            sql +=", t.manufactorys_id =" + praUs.manufactorys_id;
            sql +=", t.device_stations_id =" + praUs.device_stations_id;
            sql += ", t.parameter_base_id =" + praUs.paramenter_base_id;
            sql +=", t.value_lapdat =" + praUs.value_lapdat;
            sql +=", t.value_using = '" +praUs.value_using + "'";
            sql +=", t.date_setup = to_date('" + praUs.date_setup + "','dd/mm/yyyy')"; 
            sql +=", t.date_sys = to_date('" + praUs.date_sys + "','dd/mm/yyyy')"; 
            sql +=", t.note = '" +praUs.notes + "'";
            sql+=" WHERE t.id = " + praUs.paramenter_using_id ;
            General.Lib_ExecuteNonQuery(sql);
        }
    }
    public class Dslam
    {
        public int device_id{get; set;}
        public string name {get; set;}
        public int main_sort_device_id { get; set; }
        public int sort_device_id { get; set; }
        public string name_sort_device {get; set;}
        public int manufactorys_id { get; set; }
        public string mo_ta_thiet_bi { get; set; }
    }
    public class Paramenter_Base_Dslam
    {
        public int paramenter_base_id { set; get; }
        public int device_id { set; get; }
        public int main_sort_device_id { get; set; }
        public int sort_device_id { get; set; }
        public int manufactorys_id { get; set; }
        [Required(ErrorMessage = "Thuộc tính không được để trống!")]
        public string name_paramenter {get; set;}
        [Required(ErrorMessage = "Đặc tả không được để trống!")]
        public string unit {get; set;}
        public string datatype { get; set; }
        public float value_max{get; set;}
        public string notes{get; set;}
        public int pra_meba_sort_dv_id{get; set;}
    }
    public class Paramenter_Using_Dslam
    {
        public int paramenter_using_id { set; get; }
        public int device_id { set; get; }
        public int main_sort_device_id { get; set; }
        public int sort_device_id { get; set; }
        public int manufactorys_id { get; set; }
        public int device_stations_id {get; set;}
        [Required(ErrorMessage = "Tên tham số không được để trống!")]
        public int paramenter_base_id { set; get; }
        public int value_lapdat {get; set;}
        public string datatype { get; set; } 
        [Required(ErrorMessage = "Giá trị thực tế không được để trống!")]
        public string value_using {get; set;}
        public string date_setup{get; set;}
        public string date_sys { get; set; }
        public string name_parament { get; set; }
        public string notes{get; set;}
        public string unit { get; set;}
    }
    public class Device_Stations_Parament_Using
    {
        public int device_stations_id { get; set; }
        public int device_id { get; set; }
        public string name_device_stations { get; set; }
        public string name_stations { get; set; }
        public string sort_device_name { get; set; }
        public string date_setup { get; set; }
    }
    public class Device_Stations
    {
        public int device_station_id { get; set; }
        public int device_id { get; set; }
        public int main_sort_device_id { get; set; }
        public int sort_device_id { get; set; }
        public int manufactorys_id { get; set; }
    }
}
